import pandas as pd

# 读取表格1
coupon_data = pd.read_excel('核销2.xlsx', dtype={'订单编号': str, '卡券编码': str})  # 替换为您的表格1文件路径

# 读取表格2
sales_data = pd.read_excel('抖音.xlsx', dtype={'抖音订单编号': str, '卡券编码': str})  # 替换为您的表格2文件路径

# 读取表格3
exclude_data = pd.read_excel('不参加.xlsx')  # 替换为您的表格3文件路径

newtable = coupon_data[['订单编号', '商品名称', '金度科技', '供货商承担金额', '门店承担金额', '总费用', '商品原价', '券码']].drop_duplicates()

# 合并表格2和表格1
merged_data = pd.merge(sales_data, newtable, left_on='抖音订单编号', right_on='订单编号', how='left')

# merged_data['抖音订单编号','','']
# merged_data.to_excel('合并结果.xlsx', index=False)  # 替换为您想要保存的文件路径

# merged_data = merged_data[['抖音订单编号','卡券编码','卡券名称','商品编码','商品名称','分类编码','分类名称','销售数量','销售金额']].copy()
# #
# #
# # # # 不参与活动商品
# exclude_gods = merged_data[merged_data['商品编码'].isin(exclude_data['商品编码'])].copy()
# exclude_gods['门店应承担费用'] = exclude_gods['金度科技'] + exclude_gods['供货商承担金额']+exclude_gods['门店承担金额']
# exclude_gods['金度科技应承担费用'] = 0
# exclude_gods['供货商应承担费用'] = 0

hebing = pd.merge(merged_data, exclude_data, on='商品编码', how='left', indicator=True).copy()
hebing['是否参加'] = 1
hebing.loc[hebing['_merge'] == 'left_only', '是否参加'] = 0

# hebing = hebing.drop(['_merge'],axis=1)
hebing.loc[hebing['是否参加'] == 1, '供货商实际承担费用'] = merged_data['销售金额'] / merged_data.groupby('券码')['销售金额'].transform(
    'sum') * merged_data['供货商承担金额']
hebing.loc[hebing['是否参加'] == 1, '金度科技实际承担费用'] = merged_data['金度科技']
hebing.loc[hebing['是否参加'] == 1, '门店实际承担费用'] = merged_data['门店承担金额']

hebing.loc[hebing['是否参加'] == 0, '供货商实际承担费用'] = 0
hebing.loc[hebing['是否参加'] == 0, '金度科技实际承担费用'] = 0
hebing.loc[hebing['是否参加'] == 0, '门店实际承担费用'] = (merged_data['销售金额'] / merged_data.groupby('券码')['销售金额'].transform(
    'sum') * merged_data['门店承担金额']) + (
        merged_data['销售金额'] / merged_data.groupby('券码')['销售金额'].transform(
    'sum') * merged_data['供货商承担金额']) + (merged_data['销售金额'] / merged_data.groupby('券码')['销售金额'].transform(
    'sum') * merged_data['金度科技'])
hebing['供货商实际承担费用'] = hebing['供货商实际承担费用'].astype(float)
hebing['金度科技实际承担费用'] = hebing['金度科技实际承担费用'].astype(float)
hebing['门店实际承担费用'] = hebing['门店实际承担费用'].astype(float)
hebing['券码'] = hebing['券码'].astype(str)

# hebing['供货商实际承担费用']= hebing.groupby
# hebing['金度科技实际承担费用']=
# hebing['门店实际承担费用']=
# # # # 参与活动商品
# join_gods = merged_data[~merged_data['商品编码'].isin(exclude_data['商品编码'])].copy()
#
# # 计算供应商应承担费用
# join_gods['供货商应承担费用'] = merged_data['销售金额'] / merged_data.groupby('券码')['销售金额'].transform('sum') * merged_data[
#     '供货商承担金额']
# join_gods['金度科技应承担费用'] = 0
# join_gods['供货商应承担费用'] = 0

# result = pd.concat([exclude_gods, join_gods], axis=0, ignore_index=True)

hebing.to_excel("最后测试.xlsx", index=False)

# # 计算门店、今度科技、供应商各自承担的费用
# merged_data['金度科技实际承担'] = merged_data['金度科技']
# merged_data['供货商实际承担'] = merged_data['供货商应承担费用']
# merged_data['门店实际承担'] = merged_data['门店承担金额']
#
# exce2 = merged_data

# # # 保留不参加活动的商品
# merged_data = merged_data[~merged_data['商品编码'].isin(exclude_data['商品编码'])]
# # 计算门店、今度科技、供应商各自承担的费用
# merged_data['金度科技实际承担'] = 0
# merged_data['供货商实际承担'] = 0
# merged_data['门店实际承担'] = merged_data['门店承担金额']+merged_data['金度科技']+merged_data['供货商应承担费用']
#
# exce1 = merged_data
#
#
# result = pd.concat([exce1, exce2], axis=0, ignore_index=True)
#
#
#
# # # 以订单为单位，计算各方承担的总费用
# total_costs = result.groupby('抖音订单编号').agg({
#     '金度科技实际承担': 'sum',
#     '供货商实际承担': 'sum',
#     '门店实际承担': 'sum',
#     '商品编码':'first',
#     '商品名称_x':'first'
# }).reset_index()
# 重命名列，以更好地表示数据
# result.rename(columns={
#     '金度科技实际承担': '总金度科技承担费用',
#     '供应商实际承担': '总供应商承担费用',
#     '门店实际承担': '总门店承担费用',
#     '商品编号': '商品编号明细',
#     '商品名称': '商品名称明细'
# }, inplace=True)
# # 保存结果到新的Excel文件
# total_costs.to_excel('最终结果.xlsx', index=False)  # 替换为您想要保存的文件路径
# #
# print("处理完成，最终结果已保存到 '最终结果.xlsx'")
